package jdbc;

import java.sql.*;

/**
 * Created by wang.bh
 */
public class Jdbb {
    private int isolationLevel = Connection.TRANSACTION_READ_COMMITTED;

    public Connection getConnection() throws SQLException{
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {

        }

        Connection conn;

        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");

        System.out.println("connect to oracle.");
        return conn;
    }

    public void viewTable(Connection conn) throws SQLException{
        String query = "select * from USER_INFO where AGE between 10 and 200";

        System.out.println("view table <<<");
        try ( Statement stmt = conn.createStatement() ) {

            ResultSet rs = stmt.executeQuery(query);
            while (rs.next()) {
                String name = rs.getString("UNAME");
                String pass = rs.getString("UPASS");
                int age = rs.getInt("AGE");
                System.out.printf("%s %s %d\n", name, pass, age);
            }

        }
    }

    public void transA() {
        try (Connection conn = getConnection() ){
            conn.setAutoCommit(false);
            conn.setTransactionIsolation(getIsolationLevel());
            viewTable(conn);

            sleep(5_000);

            viewTable(conn);


            conn.commit();
        } catch (SQLException e) {
            System.out.println("mission A failed" + e.getMessage());
        }
    }


    public void repeatable() {
        System.out.println("update start");
        try (Connection conn = getConnection() ) {
            conn.setAutoCommit(false);

            Statement stmt = conn.createStatement();
            stmt.executeUpdate("update USER_INFO set AGE = AGE + 10 where UNAME like 'wang%'");

            conn.commit();
        } catch (SQLException e) {
            System.out.println("mission B failed");
        }
    }

    public void phantom(int id) {
        System.out.println("insert begin");
        try (Connection conn = getConnection() ) {
            conn.setAutoCommit(false);

            PreparedStatement stmt = conn.prepareStatement("insert into USER_INFO values(?, 'tang', '4008', 10)");
            stmt.setInt(1, id);
            stmt.executeUpdate();

            conn.commit();
        } catch (SQLException e) {
            System.out.println("mission C failed");
        }
    }

    public static void sleep(int millis) {
        try {
            Thread.sleep(millis);
        } catch (InterruptedException e) {
            System.out.println("SIGINT");
        }
    }

    public int getIsolationLevel() {
        return isolationLevel;
    }

    public void setIsolationLevel(int isolationLevel) {
        this.isolationLevel = isolationLevel;
    }

    public static void main(String[] args) throws SQLException {

    }
}
